package support;

import java.sql.*;

import javax.sql.*;
import javax.naming.*;
import java.util.Calendar;
import java.util.LinkedList;

public class DatabaseAccessObject {
	
	private Connection conn = null;
	
	private void connect() {
		try{
	    	// Obtain the environment naming context
	    	Context initCtx = new InitialContext();
	    	// Look up the data source
	    	DataSource ds = (DataSource)initCtx.lookup("java:comp/env/jdbc/CSE135");
	    	// Allocate and use a connection from the pool
	    	conn = ds.getConnection();
		}
		catch (Exception e){
			e.getStackTrace();
		}
	}
	
	public LinkedList<ApplicantName> getAllApplicantName()
	{
	    Statement stmt = null;
	    ResultSet rs = null;
	    LinkedList<ApplicantName> applicants = null;
	    try {
	    	connect();
	    	String sql = "select a.name_first, a.name_mid, a.name_last, a.id from applicants a";
	    	stmt = conn.createStatement();        
			rs = stmt.executeQuery(sql);
			applicants = new LinkedList<ApplicantName>();		
			while(rs.next())
			{
				applicants.add(new ApplicantName(
						rs.getInt(4), 
						rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3))
						);
			}
			rs.close();
			stmt.close();
			conn.close();
	     }
	    catch (Exception e){
	    	e.getStackTrace();
	    }
	    return applicants;
	}
	public Applicant getApplicantById(int id) {
		PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    Applicant a = null;
	    try {
	    	connect();
	    	String sql = "SELECT id, name_first, name_last, name_mid, citizenship AS city_id, " +
	    			"city.country AS city_name, residence AS res_id, res.country AS res_name, addr_street, " +
	    			"addr_city, addr_state, addr_zip, phone_country, phone_area, phone_number, " +
	    			"a.specialization AS spec_id, spec.specialization AS spec_name " +
	    			"FROM applicants AS a, countries AS city, countries AS res, specializations AS spec " +
	    			"WHERE a.citizenship = city.c_id " +
	    			"AND a.residence = res.c_id " +
	    			"AND a.specialization = spec.s_id " +
	    			"AND id = ?";
	    	pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				a = new Applicant();
				a.setId(rs.getInt("id"));
				a.setName(new Name(rs.getString("name_first"), rs.getString("name_last"), rs.getString("name_mid")));
				a.setCitizenship(new Country(rs.getInt("city_id"), rs.getString("city_name")));
				a.setResidence(new Country(rs.getInt("res_id"), rs.getString("res_name")));
				a.setAddress(new Address(
						rs.getString("addr_street"), 
						rs.getString("addr_city"),
						rs.getString("addr_state"),
						rs.getString("addr_zip"),
						rs.getString("phone_country"),
						rs.getString("phone_area"),
						rs.getString("phone_number")
						));
				a.setSpecialization(new Specialization(rs.getInt("spec_id"), rs.getString("spec_name")));
				a.setDegrees(getDegreesByApplicantId(a.getId()));
			}
			rs.close();
			pstmt.close();
			conn.close();
	     }
	    catch (Exception e){
	    	e.getStackTrace();
	    }
	    return a;
	}
	
	public LinkedList<ApplicantName> getApplicantNameByMajor(int major_id)
	{
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    LinkedList<ApplicantName> applicants = null;
	    try {
	    	connect();
	    	String sql = "SELECT a.name_first, a.name_mid, a.name_last, a.id from applicants a where exists ( select * FROM degrees d WHERE d.applicant = a.id AND d.major = ?)";
	    	pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1,major_id);
			rs = pstmt.executeQuery();
			applicants = new LinkedList<ApplicantName>();		
			while(rs.next())
			{
				applicants.add(new ApplicantName(
						rs.getInt(4), 
						rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3))
						);
			}
			rs.close();
			pstmt.close();
			conn.close();
	     }
	    catch (Exception e){
	    	e.getStackTrace();
	    }
	    return applicants;
	}

	public LinkedList<ApplicantName> getApplicantNameBySpecialization(int specialization_id)
	{
	    PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    LinkedList<ApplicantName> applicants = null;
	    try {
	    	connect();
	    	String sql = "SELECT a.name_first, a.name_mid, a.name_last, a.id FROM applicants a WHERE a.specialization = ?";
	    	pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1,specialization_id);
			rs = pstmt.executeQuery();
			applicants = new LinkedList<ApplicantName>();		
			while(rs.next())
			{
				applicants.add(new ApplicantName(
						rs.getInt(4), 
						rs.getString(1) + " " + rs.getString(2) + " " + rs.getString(3))
						);
			}
			rs.close();
			pstmt.close();
			conn.close();
	     }
	    catch (Exception e){
	    	e.getStackTrace();
	    }
	    return applicants;
	}
	public LinkedList<Country> getCountries()
	{
		Statement ps = null;
		ResultSet rs = null; 
		LinkedList<Country> countries = new LinkedList<Country>() ;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT * FROM countries");
			while(rs.next())
			{
				countries.add(new Country(rs.getInt(1), rs.getString(2)));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return countries;
	}
	private LinkedList<Degree> getDegreesByApplicantId(int id) {
		LinkedList<Degree> degrees = new LinkedList<Degree>();
		PreparedStatement pstmt = null;
	    ResultSet rs = null;
	    try {
	    	connect();
	    	String sql = "SELECT title, awarded_date, gpa, d.major AS major_id, m.major AS major_name , " +
	    			"d.university AS univ_id, u.university AS univ_name, u.country_state AS univ_location, applicant " +
	    			"FROM degrees AS d, majors AS m, universities AS u " +
	    			"WHERE d.major = m.m_id " +
	    			"AND d.university = u. u_id " +
	    			"AND applicant = ?";
	    	pstmt = conn.prepareStatement(sql);
	        pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				Degree d = new Degree();
				Calendar cal = Calendar.getInstance();
				cal.setTime(rs.getDate("awarded_date"));
				d.setTitle(rs.getString("title"));
				d.setMonth(cal.get(Calendar.MONTH));
				d.setYear(cal.get(Calendar.YEAR));
				d.setGpa(rs.getFloat("gpa"));
				d.setMajor(new Major(rs.getInt("major_id"), rs.getString("major_name")));
				d.setUniversity(new University(
						rs.getInt("univ_id"), 
						rs.getString("univ_name"), 
						new Location(0, rs.getString("univ_location"))
						));
				degrees.add(d);
			}
			rs.close();
			pstmt.close();
			conn.close();
	     }
	    catch (Exception e){
	    	e.getStackTrace();
	    }
		return degrees;
	}
	
	public int getLocationIdByName(String location)
	{
		Statement ps = null;
		ResultSet rs = null; 
		int id = -1;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT u_id FROM universities WHERE country_state = '"+ location +"'");
			if(rs.next())
			{
				id = rs.getInt(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;
	}
	public LinkedList<Location> getLocations()
	{
		Statement ps = null;
		ResultSet rs = null; 
		LinkedList<Location> locations = new LinkedList<Location>() ;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT country_state, MIN(u_id) AS id FROM universities AS u GROUP BY country_state ORDER BY id");
			while(rs.next())
			{
				locations.add(new Location(rs.getInt(2),rs.getString(1)));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return locations;
	}
	public int getMajorIdByName(String major)
	{
		Statement ps = null;
		ResultSet rs = null; 
		int id = -1;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT m_id FROM majors WHERE major = '"+ major +"'");
			if(rs.next())
			{
				id = rs.getInt(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;
	}

	public String getMajorNameById(int id)
	{
		Statement ps = null;
		ResultSet rs = null; 
		String major = null;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT major FROM majors where m_id = "+ id);
			if(rs.next())
			{
				major = rs.getString(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return major;		
	}
	public LinkedList<Major> getMajors()
	{
		Statement ps = null;
		ResultSet rs = null; 
		LinkedList<Major> majors = new LinkedList<Major>() ;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT * FROM majors");
			while(rs.next())
			{
				majors.add(new Major(rs.getInt(1),rs.getString(2)));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return majors;
	}
	public String getSpecializationNameById(int id)
	{
		Statement ps = null;
		ResultSet rs = null; 
		String spec = null;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT specialization FROM specializations WHERE s_id = "+ id);
			if(rs.next())
			{
				spec = rs.getString(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return spec;		
	}
	public LinkedList<Specialization> getSpecializations()
	{
		Statement ps = null;
		ResultSet rs = null; 
		LinkedList<Specialization> specializations = new LinkedList<Specialization>() ;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT * FROM specializations");
			while(rs.next())
			{
				specializations.add(new Specialization(rs.getInt(1),rs.getString(2)));
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return specializations;
	}
	public LinkedList<University> getUniversitiesByLocation(String location)
	{
		Statement ps = null;
		ResultSet rs = null; 
		Location loc = null;
		University university = null;
		LinkedList<University> universities= new LinkedList<University>();
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT * FROM universities WHERE country_state = '"+ location +"'");
			while(rs.next())
			{
				loc = new Location(rs.getInt(1),rs.getString(3));
				university = new University(rs.getInt(1),rs.getString(2),loc);
				universities.add(university);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return universities ;
	}
	public int getUniversityIdByName(String university,String location)
	{
		Statement ps = null;
		ResultSet rs = null; 
		int id = -1;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT u_id FROM universities WHERE university = '"+ university +"' and country_state = '"+ location +"'");
			if(rs.next())
			{
				id = rs.getInt(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return id;
	}
	public String getUniversityNameById(int id)
	{
		Statement ps = null;
		ResultSet rs = null; 
		String university = null;
		try {
			connect();
			ps = conn.createStatement();
			rs = ps.executeQuery("SELECT university FROM universities where u_id = "+ id);
			if(rs.next())
			{
				university = rs.getString(1);
			}
			rs.close();
			ps.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return university;		
	}
	public int newApplicant(Applicant a) {
		ResultSet rs = null;
		int appId = 0;
		
		try {
            connect();
			conn.setAutoCommit(false);
			
			// Generate SQL statement for adding new applicant
			PreparedStatement psa = conn.prepareStatement("INSERT INTO applicants(name_first,name_last,name_mid,citizenship,residence,addr_street,addr_city,addr_state,addr_zip,phone_country,phone_area,phone_number,specialization)" +
					" VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
			psa.setString(1, a.getName().getFirst());
			psa.setString(2, a.getName().getLast());
			psa.setString(3, a.getName().getMid());
			psa.setInt(4, a.getCitizenship().getId());
			psa.setInt(5, a.getResidence().getId());
			psa.setString(6, a.getAddress().getStreet());
			psa.setString(7, a.getAddress().getCity());
			psa.setString(8, a.getAddress().getState());
			psa.setString(9, a.getAddress().getZipcode());
			psa.setString(10, a.getAddress().getCountrycode());
			psa.setString(11, a.getAddress().getAreacode());
			psa.setString(12, a.getAddress().getPhone());
			psa.setInt(13, a.getSpecialization().getId());
			psa.executeUpdate();
			
			rs = psa.getGeneratedKeys();
	        rs.next();
			appId = rs.getInt("id");
			
			// Iterate all degrees belongs to the applicant
			for (Degree d : a.getDegrees()) {
				int majorId;
				int univId;
				if (d.getMajor().getId() != -1){		// User choose the existing major
					majorId = d.getMajor().getId();
				} 
				else {									// User choose the non-existing major
					// Check whether the user-input major already exists in database or not
					PreparedStatement psMajorCheck = conn.prepareStatement("SELECT m_id FROM majors WHERE major=?");
					psMajorCheck.setString(1, d.getMajor().getName());
					rs = psMajorCheck.executeQuery();
					if(rs.next()){
						majorId = rs.getInt(1);
					}
					else {
						// Generate SQL statement for adding new major
						PreparedStatement psMajor = conn.prepareStatement("INSERT INTO majors(major) VALUES(?)", Statement.RETURN_GENERATED_KEYS);
						psMajor.setString(1, d.getMajor().getName());
						psMajor.executeUpdate();
						rs = psMajor.getGeneratedKeys();
				        rs.next();
				        majorId = rs.getInt(1);
					}
				}
				
				University u = d.getUniversity();
				if (u.getId() != -1){ 					// User choose the existing university
					univId = u.getId();
				} 
				else {									// User choose the non-existing university
					// Check whether the user-input university already exists in database or not
					PreparedStatement psUnivCheck = conn.prepareStatement(
							"SELECT u_id FROM universities WHERE university = ? AND country_state = ?");
					psUnivCheck.setString(1, u.getName());
					psUnivCheck.setString(2, u.getLocation().getName());
					rs = psUnivCheck.executeQuery();
					if(rs.next()){
						univId = rs.getInt(1);
					}
					else {			
						// Generate SQL statement for adding new univesity
						PreparedStatement psUniv = conn.prepareStatement(
								"INSERT INTO universities(university,country_state) " +
								"VALUES(?,?)", Statement.RETURN_GENERATED_KEYS);
						psUniv.setString(1, u.getName());
						psUniv.setString(2, u.getLocation().getName());
						psUniv.executeUpdate();
						rs = psUniv.getGeneratedKeys();
				        rs.next();
				        univId = rs.getInt(1);
				        psUniv.close();
					}
				}
				
				// Generate SQL statement for adding new degree
				PreparedStatement psDegree = conn.prepareStatement(
						"INSERT INTO degrees(title,awarded_date,gpa,major,university,applicant) " +
						"VALUES(?,?,?,?,?,?)");
	
				Calendar cal = Calendar.getInstance();
				cal.set(d.getYear(), d.getMonth(), 1);
				psDegree.setString(1, d.getTitle());
				psDegree.setDate(2, new Date(cal.getTimeInMillis()));
				psDegree.setFloat(3, d.getGpa());
				psDegree.setInt(4, majorId);
				psDegree.setInt(5, univId);
				psDegree.setInt(6, appId);
				
				psDegree.executeUpdate();
				psDegree.close();
			}
			
			// Send all SQL statement in single transaction
			conn.commit();
			conn.setAutoCommit(true);
            conn.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
		return appId;
	}
}
